#include "adminui.h"
#include "ui_adminui.h"

adminui::adminui(QSqlDatabase* db,QString account,QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::adminui)
{
    ui->setupUi(this);
    //初始化成员变量
    m_actionList.append(ui->actStudent);
    m_actionList.append(ui->actTeacher);
    m_actionList.append(ui->actCourse);
    m_actionList.append(ui->actGrade);
    m_actionList.append(ui->actChoiceCourse);
    m_actionList.append(ui->actEva);
    m_actionList.append(ui->actTest);
    m_actionList.append(ui->actTeachCourse);
    m_actionList.append(ui->actCredit);

    m_db = *db;
    m_account = account;

    this->setCentralWidget(ui->stackedWidget);//设置中心组件
    this->setWindowTitle("欢迎使用学生信息管理系统!@管理员"+account);

    ini();

    ui->actStudent->trigger();//设置学生管理为默认界面

}

adminui::~adminui()
{
    delete ui;
}

void adminui::on_stackedWidget_currentChanged(int arg1)
{
    for(int i = 0;i < m_actionList.count(); ++i){
        m_actionList.at(i)->setChecked(false);
        if(i == arg1){
            m_actionList.at(i)->setChecked(true);
        }
    }


}


void adminui::on_actStudent_triggered()
{
    ui->stackedWidget->setCurrentWidget(ui->pagStudent);
}


void adminui::on_actTeacher_triggered()
{
    ui->stackedWidget->setCurrentWidget(ui->pagTeacher);
}


void adminui::on_actCourse_triggered()
{
    ui->stackedWidget->setCurrentWidget(ui->pagCourse);
}


void adminui::on_actGrade_triggered()
{
    ui->stackedWidget->setCurrentWidget(ui->pagGrade);
}


void adminui::on_actChoiceCourse_triggered()
{
    ui->stackedWidget->setCurrentWidget(ui->pagChoiceCourse);
}


void adminui::on_actEva_triggered()
{
    ui->stackedWidget->setCurrentWidget(ui->pagEva);
}


void adminui::on_actTest_triggered()
{
    ui->stackedWidget->setCurrentWidget(ui->pagTest);
}


void adminui::on_actTeachCourse_triggered()
{
    ui->stackedWidget->setCurrentWidget(ui->pagTeachCourse);
}


void adminui::on_actCredit_triggered()
{
    ui->stackedWidget->setCurrentWidget(ui->pagCredit);
}

void adminui::ini()
{
    //实例化模型
    m_studentModel = new QSqlTableModel(this);
    m_teacherModel = new QSqlTableModel(this);
    m_courseModel = new QSqlTableModel(this);
    m_gradeModel = new QSqlTableModel(this);
    m_choiceCourseModel = new QSqlTableModel(this);
    m_evaModel = new QSqlTableModel(this);
    m_tesetModel = new QSqlTableModel(this);
    m_teachCourseModel = new QSqlTableModel(this);
    m_creditModel = new QSqlTableModel(this);

    //绑定数据表
    m_studentModel->setTable("tb_student");
    m_teacherModel->setTable("tb_teacher");
    m_courseModel->setTable("tb_course");
    m_gradeModel->setTable("tb_grade");
    m_choiceCourseModel->setTable("tb_stuCourse");
    m_evaModel->setTable("tb_evaluate");
    m_tesetModel->setTable("tb_test");
    m_teachCourseModel->setTable("tb_teach");
    m_creditModel->setTable("tb_credit");

    //设置为确认提交时保存更改
    m_studentModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    m_teacherModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    m_courseModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    m_gradeModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    m_choiceCourseModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    m_evaModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    m_tesetModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    m_teachCourseModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    m_creditModel->setEditStrategy(QSqlTableModel::OnManualSubmit);

    //自动拉伸表格
    ui->tvStudent->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tvTeacher->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tvCourse->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tvGrade->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tvChoiceCourse->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tvEva->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tvTest->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tvTeachCOurse->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tvCredit->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);

    //获取数据
    m_studentModel->select();
    m_teacherModel->select();
    m_courseModel->select();
    m_gradeModel->select();
    m_choiceCourseModel->select();
    m_evaModel->select();
    m_tesetModel->select();
    m_teachCourseModel->select();
    m_creditModel->select();

    setHeaderLabels();//设置表格表头

    //绑定模型和视图
    ui->tvStudent->setModel(m_studentModel);
    ui->tvTeacher->setModel(m_teacherModel);
    ui->tvCourse->setModel(m_courseModel);
    ui->tvGrade->setModel(m_gradeModel);
    ui->tvChoiceCourse->setModel(m_choiceCourseModel);
    ui->tvEva->setModel(m_evaModel);
    ui->tvTest->setModel(m_tesetModel);
    ui->tvTeachCOurse->setModel(m_teachCourseModel);
    ui->tvCredit->setModel(m_creditModel);

    //默认按照成绩降序排序
    ui->tvGrade->sortByColumn(6,Qt::DescendingOrder);

    //初始化课程comb
    for(int i = 0;i < m_gradeModel->rowCount(); ++i){
        if(ui->tvGrade->isRowHidden(i)){
            continue;
        }
        bool contain = false;
        for(int j = 0;j < ui->combCourse->count(); ++j){
            if(ui->combCourse->itemText(j) == m_gradeModel->data(m_gradeModel->index(i,4)).toString()){
                contain = true;
                break;
            }
        }
        if(!contain){
            ui->combCourse->addItem(m_gradeModel->data(m_gradeModel->index(i,4)).toString());
        }
    }

    //初始化班级comb
    for(int i = 0; i < m_gradeModel->rowCount();++i){
        bool ok = false;
        QSqlQuery query;
        query.exec("select * from tb_student where id="+m_gradeModel->data(m_gradeModel->index(i,1)).toString()+";");
        if(query.size() == 0){
            QMessageBox::warning(0,"警告","学生信息查找失败!");
            break;
        }
        query.first();
        QString stuClass = query.value("class").toString();
        for(int j = 1;j < ui->combClass->count(); ++j){
            if(ui->combClass->itemText(j) == stuClass){
                ok = true;
                break;
            }
        }
        if(ok == false){
            ui->combClass->addItem(stuClass);
        }
    }

    //统计数据
    int totalCount = 0;
    int passCount = 0;
    double totalScore = 0;
    QList<double> theList;
    for(int i = 0;i < m_gradeModel->rowCount(); ++i){
        if(ui->tvGrade->isRowHidden(i)){
            continue;
        }

        double score = m_gradeModel->data(m_gradeModel->index(i,6)).toDouble();
        totalScore += score;
        ++totalCount;
        if(score >= 60.0){
            ++passCount;
        }
        theList.append(score);
    }

    if(totalCount == 0){
        ui->labAverage->setText(QString::asprintf("0"));
        ui->labStaDev->setText(QString::asprintf("0"));
        ui->labPassRate->setText(QString::asprintf("0"));
        return;
    }

    double average;
    double staDev;
    double passRate;

    average = totalScore / totalCount;
    double atotal = 0;
    for(int i = 0;i < theList.count();++i){
        atotal += abs(average-theList.at(i)) * abs(average-theList.at(i));
    }
    staDev = sqrt(atotal/totalCount);
    passRate = (double)passCount / (double)totalCount;

    ui->labAverage->setText(QString::asprintf("%.2f",average));
    ui->labStaDev->setText(QString::asprintf("%.2f",staDev));
    ui->labPassRate->setText(QString::asprintf("%.2f%%",passRate*100));
}

void adminui::setHeaderLabels()
{

    m_studentModel->setHeaderData(0,Qt::Horizontal,"学号",Qt::DisplayRole);
    m_studentModel->setHeaderData(1,Qt::Horizontal,"密码",Qt::DisplayRole);
    m_studentModel->setHeaderData(2,Qt::Horizontal,"名字",Qt::DisplayRole);
    m_studentModel->setHeaderData(3,Qt::Horizontal,"专业",Qt::DisplayRole);
    m_studentModel->setHeaderData(4,Qt::Horizontal,"班级",Qt::DisplayRole);
    m_studentModel->setHeaderData(5,Qt::Horizontal,"学位",Qt::DisplayRole);


    m_teacherModel->setHeaderData(0,Qt::Horizontal,"编号",Qt::DisplayRole);
    m_teacherModel->setHeaderData(1,Qt::Horizontal,"密码",Qt::DisplayRole);
    m_teacherModel->setHeaderData(2,Qt::Horizontal,"名字",Qt::DisplayRole);
    m_teacherModel->setHeaderData(3,Qt::Horizontal,"性别",Qt::DisplayRole);
    m_teacherModel->setHeaderData(4,Qt::Horizontal,"电话",Qt::DisplayRole);



    m_courseModel->setHeaderData(0,Qt::Horizontal,"编号",Qt::DisplayRole);
    m_courseModel->setHeaderData(1,Qt::Horizontal,"名称",Qt::DisplayRole);
    m_courseModel->setHeaderData(2,Qt::Horizontal,"学分",Qt::DisplayRole);
    m_courseModel->setHeaderData(3,Qt::Horizontal,"学时",Qt::DisplayRole);
    m_courseModel->setHeaderData(4,Qt::Horizontal,"类型",Qt::DisplayRole);



    m_gradeModel->setHeaderData(0,Qt::Horizontal,"编号",Qt::DisplayRole);
    m_gradeModel->setHeaderData(1,Qt::Horizontal,"学生学号",Qt::DisplayRole);
    m_gradeModel->setHeaderData(2,Qt::Horizontal,"学生名字",Qt::DisplayRole);
    m_gradeModel->setHeaderData(3,Qt::Horizontal,"课程编号",Qt::DisplayRole);
    m_gradeModel->setHeaderData(4,Qt::Horizontal,"课程名称",Qt::DisplayRole);
    m_gradeModel->setHeaderData(5,Qt::Horizontal,"课程类型",Qt::DisplayRole);
    m_gradeModel->setHeaderData(6,Qt::Horizontal,"分数",Qt::DisplayRole);
    m_gradeModel->setHeaderData(7,Qt::Horizontal,"教师编号",Qt::DisplayRole);
    m_gradeModel->setHeaderData(8,Qt::Horizontal,"教师姓名",Qt::DisplayRole);


    m_choiceCourseModel->setHeaderData(0,Qt::Horizontal,"编号",Qt::DisplayRole);
    m_choiceCourseModel->setHeaderData(1,Qt::Horizontal,"学生学号",Qt::DisplayRole);
    m_choiceCourseModel->setHeaderData(2,Qt::Horizontal,"学生名字",Qt::DisplayRole);
    m_choiceCourseModel->setHeaderData(3,Qt::Horizontal,"课程编号",Qt::DisplayRole);
    m_choiceCourseModel->setHeaderData(4,Qt::Horizontal,"课程名称",Qt::DisplayRole);
    m_choiceCourseModel->setHeaderData(5,Qt::Horizontal,"课程类型",Qt::DisplayRole);
    m_choiceCourseModel->setHeaderData(6,Qt::Horizontal,"老师编号",Qt::DisplayRole);
    m_choiceCourseModel->setHeaderData(7,Qt::Horizontal,"老师名字",Qt::DisplayRole);


    m_evaModel->setHeaderData(0,Qt::Horizontal,"编号",Qt::DisplayRole);
    m_evaModel->setHeaderData(1,Qt::Horizontal,"学生学号",Qt::DisplayRole);
    m_evaModel->setHeaderData(2,Qt::Horizontal,"学生名字",Qt::DisplayRole);
    m_evaModel->setHeaderData(3,Qt::Horizontal,"老师编号",Qt::DisplayRole);
    m_evaModel->setHeaderData(4,Qt::Horizontal,"老师名字",Qt::DisplayRole);
    m_evaModel->setHeaderData(5,Qt::Horizontal,"评语",Qt::DisplayRole);
    m_evaModel->setHeaderData(6,Qt::Horizontal,"评分",Qt::DisplayRole);


    m_tesetModel->setHeaderData(0,Qt::Horizontal,"编号",Qt::DisplayRole);
    m_tesetModel->setHeaderData(1,Qt::Horizontal,"学生学号",Qt::DisplayRole);
    m_tesetModel->setHeaderData(2,Qt::Horizontal,"学生名字",Qt::DisplayRole);
    m_tesetModel->setHeaderData(3,Qt::Horizontal,"课程编号",Qt::DisplayRole);
    m_tesetModel->setHeaderData(4,Qt::Horizontal,"课程名称",Qt::DisplayRole);
    m_tesetModel->setHeaderData(5,Qt::Horizontal,"课程类型",Qt::DisplayRole);
    m_tesetModel->setHeaderData(6,Qt::Horizontal,"考试时间",Qt::DisplayRole);


    m_teachCourseModel->setHeaderData(0,Qt::Horizontal,"编号",Qt::DisplayRole);
    m_teachCourseModel->setHeaderData(1,Qt::Horizontal,"老师编号",Qt::DisplayRole);
    m_teachCourseModel->setHeaderData(2,Qt::Horizontal,"老师名字",Qt::DisplayRole);
    m_teachCourseModel->setHeaderData(3,Qt::Horizontal,"课程编号",Qt::DisplayRole);
    m_teachCourseModel->setHeaderData(4,Qt::Horizontal,"课程名称",Qt::DisplayRole);
    m_teachCourseModel->setHeaderData(5,Qt::Horizontal,"课程类型",Qt::DisplayRole);


    m_creditModel->setHeaderData(0,Qt::Horizontal,"编号",Qt::DisplayRole);
    m_creditModel->setHeaderData(1,Qt::Horizontal,"专业名称",Qt::DisplayRole);
    m_creditModel->setHeaderData(2,Qt::Horizontal,"学分要求",Qt::DisplayRole);

}


void adminui::on_btnAppendChoiceCourse_clicked()
{
    m_choiceCourseModel->insertRow(m_choiceCourseModel->rowCount());
}


void adminui::on_btnDeleteChoiceCourse_clicked()
{
    if(ui->tvChoiceCourse->currentIndex().isValid()){
        m_choiceCourseModel->removeRow(ui->tvChoiceCourse->currentIndex().row());
    }
}


void adminui::on_btnResetChoiceCourse_clicked()
{
    m_choiceCourseModel->select();
}


void adminui::on_btnSubmitChoiceCourse_clicked()
{
    if(m_choiceCourseModel->isDirty()){
        if(m_choiceCourseModel->submitAll()){
            QMessageBox::information(0,"提示","提交成功!");
        }
    }
}


void adminui::on_btnAppdenCourse_clicked()
{
    m_courseModel->insertRow(m_courseModel->rowCount());
}


void adminui::on_btnDeleteCourse_clicked()
{
    if(ui->tvCourse->currentIndex().isValid()){
        m_courseModel->removeRow(ui->tvCourse->currentIndex().row());
    }
}


void adminui::on_btnResetCourse_clicked()
{
    m_courseModel->select();
}


void adminui::on_btnSubmitCourse_clicked()
{
    if(m_courseModel->isDirty()){
        if(m_courseModel->submitAll()){
            QMessageBox::information(0,"提示","提交成功!");
        }
    }
}


void adminui::on_btnAppendCredit_clicked()
{
    m_creditModel->insertRow(m_creditModel->rowCount());
}


void adminui::on_btnDeleteCredit_clicked()
{
    if(ui->tvCredit->currentIndex().isValid()){
        m_creditModel->removeRow(ui->tvCredit->currentIndex().row());
    }
}


void adminui::on_btnResetCredit_clicked()
{
    m_creditModel->select();
}


void adminui::on_btnSubmitCredit_clicked()
{
    if(m_creditModel->isDirty()){
        if(m_creditModel->submitAll()){
            QMessageBox::information(0,"提示","提交成功!");
        }
    }
}


void adminui::on_btnAppendEva_clicked()
{
    m_evaModel->insertRow(m_evaModel->rowCount());
}


void adminui::on_btnDeleteEva_clicked()
{
    if(ui->tvEva->currentIndex().isValid()){
        m_evaModel->removeRow(ui->tvEva->currentIndex().row());
    }
}


void adminui::on_btnResetEva_clicked()
{
    m_evaModel->select();
}


void adminui::on_btnSubmitEva_clicked()
{
    if(m_evaModel->isDirty()){
        if(m_evaModel->submitAll()){
            QMessageBox::information(0,"提示","提交成功!");
        }
    }
}


void adminui::on_btnAppendGrade_clicked()
{
    m_gradeModel->insertRow(m_gradeModel->rowCount());
}


void adminui::on_btnDeleteGrade_clicked()
{
    if(ui->tvGrade->currentIndex().isValid()){
        m_gradeModel->removeRow(ui->tvGrade->currentIndex().row());
    }
}


void adminui::on_btnResetGrade_clicked()
{
    m_gradeModel->select();
}


void adminui::on_btnSubmitGrade_clicked()
{
    if(m_gradeModel->isDirty()){
        if(m_gradeModel->submitAll()){
            QMessageBox::information(0,"提示","提交成功!");
        }
    }
}


void adminui::on_btnAppendStudent_clicked()
{
    m_studentModel->insertRow(m_studentModel->rowCount());
}


void adminui::on_btnDeleteStudent_clicked()
{
    if(ui->tvStudent->currentIndex().isValid()){
        m_studentModel->removeRow(ui->tvStudent->currentIndex().row());
    }
}


void adminui::on_btnResetStudent_clicked()
{
    m_studentModel->select();
}


void adminui::on_btnSubmitStudent_clicked()
{
    if(m_studentModel->isDirty()){
        if(m_studentModel->submitAll()){
            QMessageBox::information(0,"提示","提交成功!");
        }
    }
}


void adminui::on_btnAppendTeachCourse_clicked()
{
    m_teachCourseModel->insertRow(m_teachCourseModel->rowCount());
}


void adminui::on_btnDeleteTeachCourse_clicked()
{
    if(ui->tvTeachCOurse->currentIndex().isValid()){
        m_teachCourseModel->removeRow(ui->tvTeachCOurse->currentIndex().row());
    }
}


void adminui::on_btnResetTeachCourse_clicked()
{
    m_teachCourseModel->select();
}


void adminui::on_btnSubmitTeachCOurse_clicked()
{
    if(m_teachCourseModel->isDirty()){
        if(m_teachCourseModel->submitAll()){
            QMessageBox::information(0,"提示","提交成功!");
        }
    }
}


void adminui::on_btnAppendTeacher_clicked()
{
    m_teacherModel->insertRow(m_teacherModel->rowCount());
}


void adminui::on_btnDeleteTeacher_clicked()
{
    if(ui->tvTeacher->currentIndex().isValid()){
        m_teacherModel->removeRow(ui->tvTeacher->currentIndex().row());
    }
}


void adminui::on_btnResetTeacher_clicked()
{
    m_teacherModel->select();
}


void adminui::on_btnSubmitTeacher_clicked()
{
    if(m_teacherModel->isDirty()){
        if(m_teacherModel->submitAll()){
            QMessageBox::information(0,"提示","提交成功!");
        }
    }
}


void adminui::on_btnAppendTest_clicked()
{
    m_tesetModel->insertRow(m_tesetModel->rowCount());
}


void adminui::on_btnDeleteTest_clicked()
{
    if(ui->tvTest->currentIndex().isValid()){
        m_tesetModel->removeRow(ui->tvTest->currentIndex().row());
    }
}


void adminui::on_btnResetTest_clicked()
{
    m_tesetModel->select();
}


void adminui::on_btnSubmitTest_clicked()
{
    if(m_tesetModel->isDirty()){
        if(m_tesetModel->submitAll()){
            QMessageBox::information(0,"提示","提交成功!");
        }
    }
}


void adminui::on_actQuit_triggered()
{
    this->close();
}


void adminui::on_btnScreen_clicked()
{
    //显示所有行
    for(int i = 0;i < m_gradeModel->rowCount(); ++i){
        ui->tvGrade->showRow(i);
    }

    //筛选行
    //通过课程名称筛选
    if(ui->combCourse->currentText() != "所有课程"){
        for(int i = 0;i < m_gradeModel->rowCount(); ++i){
            if(ui->tvGrade->isRowHidden(i)){
                continue;
            }else{
                if(m_gradeModel->data(m_gradeModel->index(i,4)).toString() != ui->combCourse->currentText()){
                    ui->tvGrade->hideRow(i);
                }
            }
        }
    }

    //通过分数区间筛选
    if((ui->spinScoreMin->value() != 0 || ui->spinScoreMax->value() != 0) && (ui->spinScoreMin->value() <= ui->spinScoreMax->value())){
        for(int i = 0;i < m_gradeModel->rowCount(); ++i){
            if(ui->tvGrade->isRowHidden(i)){
                continue;
            }else{
                if(!(m_gradeModel->data(m_gradeModel->index(i,6)).toDouble() >= ui->spinScoreMin->value() && m_gradeModel->data(m_gradeModel->index(i,6)).toDouble() <= ui->spinScoreMax->value())){
                    ui->tvGrade->hideRow(i);
                }
            }
        }
    }

    //通过班级筛选
    QSqlQuery query;
    if(ui->combClass->currentText() != "所有班级"){
        for(int i = 0; i < m_gradeModel->rowCount(); ++i){
            if(ui->tvGrade->isRowHidden(i)){
                continue;
            }else{
                query.exec("select * from tb_student where id="+m_gradeModel->data(m_gradeModel->index(i,1)).toString()+";");
                query.first();
                if(ui->combClass->currentText() != query.value("class")){
                    ui->tvGrade->hideRow(i);
                }
            }
        }
    }

    //排序
    if(ui->btnAsc->isChecked()){
        ui->tvGrade->sortByColumn(6,Qt::AscendingOrder);
    }else{
        ui->tvGrade->sortByColumn(6,Qt::DescendingOrder);
    }

    //统计数据
    int totalCount = 0;
    int passCount = 0;
    double totalScore = 0;
    QList<double> theList;
    for(int i = 0;i < m_gradeModel->rowCount(); ++i){
        if(ui->tvGrade->isRowHidden(i)){
            continue;
        }

        double score = m_gradeModel->data(m_gradeModel->index(i,6)).toDouble();
        totalScore += score;
        ++totalCount;
        if(score >= 60.0){
            ++passCount;
        }
        theList.append(score);
    }

    if(totalCount == 0){
        ui->labAverage->setText(QString::asprintf("0"));
        ui->labStaDev->setText(QString::asprintf("0"));
        ui->labPassRate->setText(QString::asprintf("0"));
        return;
    }

    double average;
    double staDev;
    double passRate;

    average = (double)totalScore / (double)totalCount;
    double atotal = 0;
    for(int i = 0;i < theList.count();++i){
        atotal += abs(average-theList.at(i)) * abs(average-theList.at(i));
    }
    staDev = sqrt(atotal/totalCount);
    passRate = (double)passCount / (double)totalCount;

    ui->labAverage->setText(QString::asprintf("%.2f",average));
    ui->labStaDev->setText(QString::asprintf("%.2f",staDev));
    ui->labPassRate->setText(QString::asprintf("%.2f%%",passRate*100));
}


void adminui::on_btnFind_clicked()
{
    if(!ui->editFind->text().isEmpty()){
        for(int i = 0; i < m_gradeModel->rowCount(); ++i){
            for(int j = 0; j < m_gradeModel->columnCount(); ++j){
                if(!ui->tvGrade->isRowHidden(i) && m_gradeModel->data(m_gradeModel->index(i,j)).toString().contains(ui->editFind->text())){
                    ui->tvGrade->selectRow(i);
                }
            }
        }
    }
}

